/*
 * Copyright (C), 2013-2017, 工人网
 * FileName: ExcelUtils.java
 * Author:   jgYang
 * Date:     2017年6月19日 上午10:05:39
 * Description: //模块目的、功能描述      
 * History: //修改记录
 * <author>      <time>      <version>    <desc>
 * 修改人姓名             修改时间            版本号                  描述
 */
package com.dlg.commons.util;

import com.alibaba.fastjson.JSONObject;
import com.dlg.commons.annotation.ChineseConverter;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.util.StringUtils;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.lang.reflect.Method;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * 功能描述: Excel工具类 Author: jgYang Date: 2017年6月19日 上午10:05:39
 */
public class ExcelUtils {

    public static synchronized List<String[]> readExcel(File file) throws IOException {
        String fileName = file.getName();
        // 根据其名称获取后缀
        String extension = fileName.lastIndexOf(".") == -1 ? "" : fileName.substring(fileName.lastIndexOf(".") + 1);
        List<String[]> result;
        if ("xls".equals(extension)) {
            result = read2003Excel(new FileInputStream(file));
        } else if ("xlsx".equals(extension) || "xlsm".equals(extension)) {
            result = read2007Excel(new FileInputStream(file));
        } else if ("tmp".equals(extension)) {
            result = read2007Excel(new FileInputStream(file));
        } else {
            throw new IOException("不支持的文件类型");
        }
        return result;
    }

    /**
     * 功能描述: 读取Office 2007 excel
     * 
     * @param file
     * @return Author: jgYang Date: 2017年6月19日 上午10:07:15
     */
    private static List<String[]> read2007Excel(FileInputStream file) throws IOException {
        List<String[]> result = new ArrayList<>();
        int rowSize = 0;
        XSSFCell cell;
        // 构造 XSSFWorkbook 对象，strPath 传入文件路径
        XSSFWorkbook xwb = new XSSFWorkbook(file);
        XSSFSheet sheet = xwb.getSheetAt(0);
        for (int rowIndex = 0; rowIndex <= sheet.getLastRowNum(); rowIndex++) {
            XSSFRow row = sheet.getRow(rowIndex);
            if (row == null) {
                String[] values = new String[rowSize];
                Arrays.fill(values, "");
                result.add(values);
                continue;
            }
            int tempRowSize = row.getLastCellNum();
            if (tempRowSize > rowSize) {
                rowSize = tempRowSize;
            }
            String[] values = new String[rowSize];
            Arrays.fill(values, "");
            boolean hasValue = false;
            for (short columnIndex = 0; columnIndex < row.getLastCellNum(); columnIndex++) {
                String value;
                cell = row.getCell(columnIndex);// 得到的每行的每个具体的值
                if (cell == null) {
                    continue;
                }
                switch (cell.getCellType()) {
                    case XSSFCell.CELL_TYPE_STRING:
                        value = cell.getStringCellValue();
                        break;
                    case XSSFCell.CELL_TYPE_NUMERIC:
                        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");// 格式化日期字符串
                        if ("@".equals(cell.getCellStyle().getDataFormatString())
                                || "General".equals(cell.getCellStyle().getDataFormatString())) {
                            value = new DecimalFormat("0").format(cell.getNumericCellValue());
                        } else {
                            value = sdf.format(HSSFDateUtil.getJavaDate(cell.getNumericCellValue()));
                        }
                        break;
                    case XSSFCell.CELL_TYPE_FORMULA:
                        // 导入时如果为公式生成的数据则无值
                        try {
                            Long number = (long) cell.getNumericCellValue();
                            value = number + "";
                        } catch (Exception e) {
                            value = cell.getStringCellValue();
                        }
                        break;
                    case XSSFCell.CELL_TYPE_BLANK:
                        value = "";
                        break;
                    case XSSFCell.CELL_TYPE_ERROR:
                        value = "";
                        break;
                    case XSSFCell.CELL_TYPE_BOOLEAN:
                        value = (cell.getBooleanCellValue() == true ? "Y" : "N");
                        break;
                    default:
                        value = "";
                        break;
                }
                values[columnIndex] = rightTrim(value);
                hasValue = true;
            }
            if (hasValue) {
                result.add(values);
            }
        }
        return result;
    }

    /**
     * 功能描述: 读取 office 2003 excel
     * 
     * @param file
     * @return Author: jgYang Date: 2017年6月19日 上午10:07:12
     */
    private static List<String[]> read2003Excel(FileInputStream file) throws IOException {
        List<String[]> result = new ArrayList<>();
        int rowSize = 0;
        // 创建新的 excel 工作博
        HSSFWorkbook hwb = new HSSFWorkbook(file);
        // System.out.println("=============== 此 " + file.getName() + " 文档一共有 " + hwb.getNumberOfSheets() + "
        // sheet...第一个sheet有" + hwb.getSheetAt(0).getLastRowNum() + " 行... ");
        HSSFCell cell;
        // 循环列
        HSSFSheet sheet = hwb.getSheetAt(0);
        // 循环每行 不包括标题 导航. 如何想要把列名也显示出来， 请把下边的1 修改为0
        for (int rowIndex = 0; rowIndex <= sheet.getLastRowNum(); rowIndex++) {
            HSSFRow row = sheet.getRow(rowIndex);// 得到每一行的数据
            if (row == null) {
                String[] values = new String[rowSize];
                Arrays.fill(values, "");
                result.add(values);
                continue;
            }
            int tempRowSize = row.getLastCellNum();
            if (tempRowSize > rowSize) {
                rowSize = tempRowSize;
            }
            String[] values = new String[rowSize];
            Arrays.fill(values, "");
            boolean hasValue = false;
            for (int columnIndex = 0; columnIndex <= row.getLastCellNum(); columnIndex++) {
                String value = "";
                cell = row.getCell(columnIndex);
                if (cell == null) {
                    continue;
                }
                switch (cell.getCellType()) {
                    case XSSFCell.CELL_TYPE_STRING:
                        value = cell.getStringCellValue();
                        break;
                    case XSSFCell.CELL_TYPE_NUMERIC:
                        if (HSSFDateUtil.isCellDateFormatted(cell)) {
                            Date date = cell.getDateCellValue();
                            if (date != null) {
                                value = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss").format(date);
                            } else {
                                value = "";
                            }
                        } else {
                            value = new DecimalFormat("0").format(cell.getNumericCellValue());
                        }
                        break;
                    case XSSFCell.CELL_TYPE_FORMULA:
                        // 导入时如果为公式生成的数据则无值
                        try {
                            Long number = (long) cell.getNumericCellValue();
                            value = number + "";
                        } catch (Exception e) {
                            value = cell.getStringCellValue();
                        }
                        break;
                    case XSSFCell.CELL_TYPE_BOOLEAN:
                        value = (cell.getBooleanCellValue() == true ? "Y" : "N");
                        break;
                    case XSSFCell.CELL_TYPE_BLANK:
                        value = "";
                        break;
                    case XSSFCell.CELL_TYPE_ERROR:
                        value = "";
                        break;
                    default:
                        value = cell.toString();
                }
                if (columnIndex == 0 && "".equals(value.trim())) {
                    break;
                }
                values[columnIndex] = rightTrim(value);
                hasValue = true;
            }
            if (hasValue) {
                result.add(values);
            }
        }
        return result;
    }

    /**
     * 功能描述: 去掉字符串右边的空格
     * 
     * @param value
     * @return Author: jgYang Date: 2017年6月19日 上午10:08:33
     */
    public static String rightTrim(String value) {
        if (value == null) {
            return "";
        }
        int length = value.length();
        for (int i = length - 1; i >= 0; i--) {
            if (value.charAt(i) != 0x20) {
                break;
            }
            length--;
        }
        return value.substring(0, length);
    }

    public static void main(String[] args) {
        File file = new File("F:\\payBatch.xlsx");
        try {
            List<String[]> list = readExcel(file);
            int temp = 1;
            for (String[] str : list) {
                StringBuffer st = new StringBuffer();
                st.append(">>>>>" + temp + "<<<<<");
                for (int i = 0; i < str.length; i++) {
                    if (!StringUtils.isEmpty(str[i]))
                        st.append(",第" + i + "列 ：" + str[i]);
                }
                System.out.println(st.toString());
                temp++;
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    /**
     * 处理导出excel状态为数字问题
     * @param clazz
     * @param list
     * @return
     */
    @SuppressWarnings("unchecked")
	public static <T> List<T> convertToChinese(Class<?> clazz, List<T> list) {
        try {
            Method[] methods = clazz.getDeclaredMethods();
            for (int i = 0; i < methods.length; i++) {
                ChineseConverter annotation = methods[i].getAnnotation(ChineseConverter.class);
                Method method = methods[i];
                if (annotation != null) {
                    // 对应的get方法名
                    String getMethodName = annotation.getMethodName();
                    // 和数字的对应关系
                    String relationShip = annotation.relationShip();
                    // 转化为map
                    Map<String, String> map = (Map<String, String>) JSONObject.parse(relationShip);
                    for (int j = 0; j < list.size(); j++) {
                        T o = list.get(j);
                        Object status = clazz.getDeclaredMethod(getMethodName).invoke(o);
                        for (Map.Entry<String, String> entry : map.entrySet()) {
                            if (entry.getKey().equals(String.valueOf(status))) {
                                method.invoke(o, entry.getValue());
                            }
                        }
                    }

                }
            }

        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }


}
